import pymysql
import pandas as pd
import pymysql.cursors
from conf import CITY_DICT

class MysqlUtils(object):
    """数据库工具类"""
    def __init__(self):
        self.conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='root',
        database='scenic',
        port=3306,
        charset='utf8'
        )

    def get_scenic_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT LEFT(u.id_no,4) as city_code,DATE_FORMAT(o.create_time,'%Y-%m') as month, count(u.id) as count FROM ticket_order_user_rel u
        JOIN ticket_order o on u.order_id=o.id WHERE LENGTH(u.id_no)=18 and o.pay_time is not null and o.pay_time != '' GROUP BY city_code, month
        """
        cursor.execute(sql)
        ret = cursor.fetchall()

    new_list = []
    for item in ret:
        if item["city_code"] not in CITY_DICT:
            continue
    new_list.append({
        'city_code': item["city_code"],
        'month': item["month"],
        'count': item["count"],
        'city_name': CITY_DICT[item["city_code"]]
    })
# print(new_list)
df_city_monthly = pd.DataFrame(new_list)
df_city_monthly['month'] = pd.to_datetime(df_city_monthly['month'] + '-01')
current_month = pd.to_datetime('2024-12-01')
def calculate_baseline(df, current_month, window_size=6):
    # 提取当前月数据
    df_current = df[df['month'] == current_month]
    # 设置历史时间范围
    history_start = current_month - pd.DateOffset(months=window_size)
    df_history = df[(df['month'] >= history_start) & (df['month'] < current_month)]

    # 按城市计算均值和标准差
    df_baseline = df_history.groupby('city_name')['count'].agg(['mean', 'std']).reset_index()
    df_baseline.rename(columns={'mean': 'hist_mean', 'std': 'hist_std'}, inplace=True)
    # 合并当前月数据
    df_merged = df_current.merge(df_baseline, on='city_name', how='left')
    return df_merged

df_merged = calculate_baseline(df_city_monthly, current_month)
# 计算z_score
df_merged['z_score'] = (df_merged['count'] - df_merged['hist_mean']) / df_merged['hist_std']

# 标记异常值的城市 z_score > 3 or z_score < -3
df_increased = df_merged[df_merged['z_score'] > 3]
df_reduce = df_merged[df_merged['z_score'] < -3]
print(df_increased)
print('----------------')
print(df_reduce)

if __name__=='__main__':
    mu=MysqlUtils()
    mu.get_scenic_data()